CS 105
Access, A "Relational" Database
Assignment 01
Note for Macintosh users: Microsoft Access is not available on Macs or any other Apple computer. Access has been around since 1991 but has only been available on Windows, unlike the other applications in the Microsoft Office Suite which were ported to Macintosh very early. So either you need to use a Windows machine on campus or ignore the database assignments. However, there are more than enough points available from other exercises.
| CIT105P Rubrics | |
| Assignment 6-7-8: Database 1, 2, 3 | |
| Tables | 10 |
| Search Query | 10 |
| Report | 10 |
| Total | 30 |
For a starter list of online video tutorials, see below (very bottom of this page)
Mr. Zajac, the owner of a small store in the village is converting his record keeping from index cards to computer. He has a box of index cards for each customer and another box of index cards for each item for sale in his store. Your job is to design the database to handle the information now stored on cards. Your analysis reveals a need for specific data entry forms and output reports. You also identify ways that sorting, filters, and queries can be used to help manage the data.
For this assignment you will create a database with tables needed to generate the reports and forms he wants.
1. Create a database called CornerStore with a table called Customers
Open Access and click on "Blank desktop database"
This will give you a new database with a single new table called, by default, "Table1." You will need to save it under a more meaningful name and to add fields (below).
As in the instructions and arrows on the image, click on the upper left triangle and ruler icon for "Design View" button.
This brings up the Table Name dialog. Type "Customers" in the text box then click the "OK" button.
Now you have a blank structure form in which you enter fieldnames and attributes.
For the table Customers, use the following field names and attributes:
| Field Name | Field Properties |
| Customer Number | AutoNumber |
| First Name | Short Text, Field Size = 20 |
| Last Name | Short Text, Field Size =50 |
| Address | Short Text, Field Size = 80 |
| City | Short Text, Field Size = 60 |
| State | Short Text, Field SIze = 30 |
| Mail Code | Short Text, Field Size = 15 |
| Phone Number | Short Text, Field Size=20 |
Note that the area to enter field attributes is at the bottom of the form. In this case we are only setting the field size attribute.
When done save (Control-S keystroke) the structure and click on the upper right "X" in the corner to close this design view.
When done your field structure should look like this. Type Control-S to save and click the "View" icon to start entering the information (see below)
At this point, after saving (step above) you will see your new table in spreadsheet view with field names along the column tops and a blank line to start entering data underneath. An asterisk (*) is at the left side of that new-record data line.
Customer number is unique for each customer record. It identifies a specific customer. In other tables we can use customer number when we want to refer to a specific customer. When other tables, such as records of invoices, use the customer number instead of a customer name, it is easy to keep records connected (related) regardless of updates to the customer record, such as name changes, spellings, addresses and so forth. The number remains the same.
Add the following data to the table Customers: Do this by typing into a field in the line with has the asterisk at the left. As soon as you start typing the text goes in and a new asterisk (new blank record) line appears below where you are typing. Do not bother typing anything into the "CustomerNumber" field. The index number here is generated automatically by the database. It is always unique. Start typing the FirstName, then the LastName and finally the PhoneNumber fields for all 5 records as shown below.
Type in the customer names and numbers above.
2 Add a table called Inventory to the database
Click on the "CREATE" tab then click on the "Table" icon.
This part shold be familiar. This step is the same as we had with the Customer table only this time we type "Merchandise" in the Table Name box.
For the table Merchandise, use the following field names and attributes:
|
Field Name |
Field Properties |
| ItemID | AutoNumber |
| Item | Short Text, Field Size = 80 |
| Tags | Short Text, Field Size = 60 |
| Description | Short Text, Field Size = 255 |
Cost |
Currency |
Supplier |
Short Text |
| SupplierID | Integer |
| Sale Price | Currency |
| DateOfPurchase | Date, Format=Short Date |
| DateOfSale | Date, Format=Short Date |
| SKU | Short Text, Field Size = 40 |
OnHand |
Long Integer |
Filled in, the structure form should look like this.
For the field attributes look at the bottom panel. In this case we are setting a date field to the "Short Date" format.
Add the following data to the table Inventory:
| ItemID | Item | Tags | Description | Cost | Supplier | Supplier ID | Selling Price | DateOfPurchase | DateLastSale | Sku | OnHand |
| 1 | Nikon D7200 Body | 7200, DSLR,APSC,24mb | Nikon 24 mp APS-C DSLR Body | $800.00 | Nikon | 123 | $1300.00 | 1/1/2016 | 12/1/2016 | NDSLR7200 | 15 |
| 2 | Power Spec | G313, Laptop | PowerSpec HP laptop computer | $600.00 | hp | 456 | $999.99 | 10/1/2016 | 1/2/2017 | hp112466 | 10 |
| 3 | Alexa | echo, alexa, dot | Alexa Echo Dot | $20.00 | Amazon | 4256 | $49.99 | 12/12/2016 | 1/3/2017 | ed927650 | 234 |
| 4 | Printer | epson,printer,wireless,expression,CD | Epson Expression XP-430 | $60.00 | Epson America | 272 | $54.99 | 11/11/2015 | 4/4/2016 | ep430exp | 25 |
| 5 | Cruiser Bike | Schwinn, bicycle,cruiser | Schwinn Mens Legacy 26 inch Cruiser Bike | $100.00 | Schwinn | 383 | $139.99 | 6/6/2016 | 1/4/2017 | SCBi26u3028 | 35 |
| 6 | LED Task Lamp | LED | LED Task Lamp for desk tops | $10.00 | Univac | 8949 | $19.99 | 8/15/2016 | 12/31/2016 | LLED234v12 | 66 |
| 8 | Hansel und Gretel - Both Nov 2009 Video DVD (NTSC) | DVD, Video, Opera | Hansel und Gretel Thursday and Friday Shows (19, 20 Nov 2009) for UMKC Conservatory Music and Dance - White Recital Hall, Performing Arts Center - both casts - 1.6 hours, in German, three acts. DVD - NTSC | $20.00 | KCD Info | 234 | $40.00 | 11/22/2009 | 11/22/2009 | HUG_BOTHNOV2009ntscDVD | 4 |
| 9 | MOOL 2006 Video DVD | DVD, Video, Dance | Music Of Our Lives - Sunday, 15 October 2006 - Musical Review featuring the music and dance of the 30's and 40's, big Band and Swing - Directed by Billie Mahoney and presented by the Foundation on Aging | $5.00 | KCD Info | 234 | $20.00 | 11/23/2009 | 11/23/2009 | MOOL2006DVD | 4 |
| 10 | Music Of Our Lives Video DVD 2004 | DVD, Video, Dance | Music Of Our Lives - Saturday and Sunday, 23/24 Oct 2004 - Musical Review featuring the music and dance of the 30's and 40's, big Band and Swing - Directed by Billie Mahoney and presented by the Foundation on Aging | $5.00 | KCD Info | 234 | $25.00 | 11/23/2009 | 11/23/2009 | mool2004102xDVD | 4 |
| 30 | Auricon 16mm movie camera, sound camera | movie camera 16mm | Auricon 16mm sound-film camera. Auricon Cinevoice 16 mm Camera with 400-foot magazine Model CM 72-A, 20-60mm zoom lens. Serial number A6-79,649, formerly used by NBC. | $1500.00 | Helix | 945 | $2500.00 | 11/27/2009 | 11/27/2009 | 16mmAuri06 | 1 |
| 32 | Actinograph - Early Densitometer from 1888 | antiques, densitometer | Antique HD (Hurter-Driffield) Actinograph (patented 1888) used to chart the density of photographic negatives. This is the source of all densitometry used today. | $250.00 | Helix | 945 | $350.00 | 11/27/2009 | 11/27/2009 | HDActino123 | 1 |
| 33 | Wild T4 Theodolite for Astronomic Surveys | surveying instrument theodolite | Classic T4 Theodolite made by Wild in Switzerland. Used in first-order class-1 astronomic surveys to determine latitude and longitude for positions. | $4500.00 | Wild | 2391 | $7500.00 | 11/27/2009 | 11/27/2009 | WTHEO34512 | 3 |
It should come out looking like this, below.
Once you have finished all steps,submit the database file for CornerStore as an attachment in your email.
(Note: Save a copy of the database CornerStore. It will be used in the next two assignments.) This is assignment DB1.
Online Helpers - Just a place to get started
Microsoft Access Tutorials on YouTube (a starter list only)
Just Google for "Microsoft Access tutorial" for more (hint, there are tons of these)
Access - making a database - Access 2016
https://www.youtube.com/watch?v=PBhftKTmdHI
Tutorial for Beginners - Access 2016
https://www.youtube.com/watch?v=qaQflNwD4hc
Beginners MS Access 2007 Database Tutorial 1 - Introduction and Creating Database
https://www.youtube.com/watch?v=BUE-XJEHp7g
Beginners MS Access 2007 Database Tutorial 2 - Table Filter, Sort & Introduction Forms
https://www.youtube.com/watch?v=_PZAqojDEIw
Beginners MS Access 2007 Database Tutorial 3 - How To Create Forms with Buttons and Charts
https://www.youtube.com/watch?v=8PiD4awKjQw
Beginners MS Access 2007 Database Tutorial 4 - Creating Queries
https://www.youtube.com/watch?v=XQ6ixJc8Q_Y
How to Create a Stock Management Database in Microsoft Access 2010
https://www.youtube.com/watch?v=9HRmSRR29fY
Same, without the distracting background music
https://www.youtube.com/watch?v=049IlFNcrl0&t=0s